package com.zhang.zxx.easyexcel.controller;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.style.WriteCellStyle;
import com.alibaba.excel.write.metadata.style.WriteFont;
import com.alibaba.excel.write.style.HorizontalCellStyleStrategy;
import com.zhang.zxx.easyexcel.pojo.ExcelListener;
import com.zhang.zxx.easyexcel.pojo.User3;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.net.URLEncoder;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * ExcelController:
 *
 * @author zhangxiaoxiang
 * @date 2021/01/13
 */
@Controller
@RequestMapping("/web/excel")
@Slf4j
public class ExcelController {
    /**
     * 浏览器下载excel
     *
     * @param response Excel输出文件
     * @throws IOException 异常
     */
    @GetMapping(value = "/download", produces = "application/json;charset=UTF-8")
    public void downloadEasyExcel(HttpServletResponse response) throws IOException {
        //EasyExcel下载步骤
        //设置响应头
        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //设置防止文件名中文乱码
        String fileName = URLEncoder.encode("中文文件名(测试下载)", "utf-8");
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");
        //构建写入到Excel中的数据(此数据可以从数据库中获取)
        //这里可以使用查询数据库的方式
        List<User3> users = new ArrayList<>();
        User3 user1 = new User3(1001, "长草颜团子1", "男", new Date());
        User3 user2 = new User3(1002, "长草颜团子2", "男", new Date());
        User3 user3 = new User3(1003, "长草颜团子3", "男", new Date());
        users.add(user1);
        users.add(user2);
        users.add(user3);
        EasyExcel.write(response.getOutputStream(), User3.class).sheet("用户信息").doWrite(users);
    }

    @GetMapping(value = "/download2", produces = "application/json;charset=UTF-8")
    public void downloadEasyExcel2(HttpServletResponse response) throws IOException {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd-HH:mm:ss");
        String datetime = sdf.format(new Date());
        String fileName = URLEncoder.encode("中文文件名(测试下载2)", "UTF-8") + datetime;

        response.setContentType("application/vnd.ms-excel");
        response.setCharacterEncoding("utf-8");
        //这里可以设置浏览器内部下载还是附件下载2种方式
        response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xlsx");

        // excel头策略(可省略)
        WriteCellStyle headWriteCellStyle = new WriteCellStyle();
        WriteFont headWriteFont = new WriteFont();
        headWriteFont.setFontHeightInPoints((short) 11);
        headWriteFont.setBold(false);
        headWriteCellStyle.setWriteFont(headWriteFont);

        // excel内容策略(可省略)
        WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
        WriteFont contentWriteFont = new WriteFont();
        contentWriteFont.setFontHeightInPoints((short) 11);
        contentWriteCellStyle.setWriteFont(contentWriteFont);

        // 设置handler
        HorizontalCellStyleStrategy styleStrategy = new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
        //这里可以使用查询数据库的方式
        List<User3> users = new ArrayList<>();
        User3 user1 = new User3(1001, "长草颜团子1", "男", new Date());
        User3 user2 = new User3(1002, "长草颜团子2", "男", new Date());
        User3 user3 = new User3(1003, "长草颜团子3", "男", new Date());
        users.add(user1);
        users.add(user2);
        users.add(user3);

        EasyExcel.write(response.getOutputStream(), User3.class)
                .sheet("下载excel服务")
                .registerWriteHandler(styleStrategy)
                .doWrite(users);
    }

    /**
     * @param serviceFile excel文件
     * @return 注意请求参数是 file
     * @throws IOException 异常
     */
    @PostMapping(value = "/upload")
    @ResponseBody
    public String uploadEasyExcel(@RequestParam(value = "file") MultipartFile serviceFile) throws IOException {
        log.warn("postman注意请求参数是 file,值是file类型,可以百度一下postman上传excel");
        ExcelReader excelReader = null;
        @Cleanup InputStream in = null;
        try {
            in = serviceFile.getInputStream();
            //注意这里需要解析的数据 User3.class
            excelReader = EasyExcel.read(in, User3.class, new ExcelListener()).build();
            ReadSheet readSheet = EasyExcel.readSheet(0).build();
            excelReader.read(readSheet);
        } catch (IOException ex) {
            log.error("import excel to db fail", ex);
        }
        // finally {
        //     in.close();
        //     // 这里一定别忘记关闭，读的时候会创建临时文件，到时磁盘会崩 @Cleanup注解更加简洁一些哈
        //     if (excelReader != null) {
        //         excelReader.finish();
        //     }
        // }
        return "解析成功,并入库";
    }
}
